﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections.Specialized;
using Tools.Other;
using Frame.Core.Users;
using System.Data.SqlClient;
using System.Data;
using Tools.SqlServer;
using Frame.IRepositories;
using Frame.IRepositories.Users;

namespace Frame.Infrastructure.Repositories.Users
{
    #region  TODO自定义区域
    public partial class UserProvider : IUserProvider  //:ProviderBase, IUserProvider
    {
        public DataSet getSomeUser(NameValueCollection nv,SqlTransaction dbt = null)
        {
            StringBuilder whereSql = new StringBuilder();
         
            List<SqlParameter> lstParam = new List<SqlParameter>();
            string sSql;
            Tools.Other.SqlWhereCreater.ToSqlFilter(nv, "UserName|RealName|ModifyDate_RANGETIME1|ModifyDate_RANGETIME2", true, lstParam, out sSql, "h.");
            whereSql.Append(sSql);
            string sql = string.Format(@"
                select * from tbUser u
                where 1=1 {0}
                ", whereSql);
            DataSet ds = SqlHelper.sqlToDataSet(sql, lstParam, dbt);
            return ds;
        }
    }
    #endregion
    #region  TODO自动生成区域修改
    public partial class UserProvider : IUserProvider  //:ProviderBase, IUserProvider
    {
    }
    #endregion
    #region  自动生成
    /// <summary>
    /// [当前中文表名]
    /// </summary>		
    public partial class UserProvider: IUserProvider //:ProviderBase, IUserProvider
    {

        #region 构造函数
        public UserProvider()
        {

            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        public static readonly string DBOwner = "";
        #endregion

        #region 函数
        private IDbDataParameter[] ValueParas(User model)
        {
            SqlParameter[] paras ={
			new SqlParameter("@UserID",SqlDbType.Int),
			new SqlParameter("@UserName",SqlDbType.NVarChar),
			new SqlParameter("@UserPassword",SqlDbType.NVarChar),
			new SqlParameter("@RoleID",SqlDbType.Int),
			new SqlParameter("@DeptID",SqlDbType.Int),
			new SqlParameter("@Phone",SqlDbType.NVarChar),
			new SqlParameter("@Fax",SqlDbType.NVarChar),
			new SqlParameter("@Email",SqlDbType.NVarChar),
			new SqlParameter("@QQ",SqlDbType.NVarChar),
			new SqlParameter("@NickName",SqlDbType.NVarChar),
			new SqlParameter("@Address",SqlDbType.NVarChar),
			new SqlParameter("@RealName",SqlDbType.NVarChar),
			new SqlParameter("@Sex",SqlDbType.Bit),
			new SqlParameter("@Enabled",SqlDbType.Bit),
			new SqlParameter("@LastLoginTime",SqlDbType.DateTime),
			new SqlParameter("@CreateUserID",SqlDbType.Int),
			new SqlParameter("@CreateDate",SqlDbType.DateTime),
			new SqlParameter("@ModifyUserID",SqlDbType.Int),
			new SqlParameter("@ModifyDate",SqlDbType.DateTime),
			new SqlParameter("@RecordStatus",SqlDbType.NVarChar),
			new SqlParameter("@IsDeleted",SqlDbType.Bit)
			};
            paras[0].Value = model.UserID;
            paras[1].Value = model.UserName;
            paras[2].Value = model.UserPassword;
            paras[3].Value = model.RoleID;
            paras[4].Value = model.DeptID;
            paras[5].Value = model.Phone;
            paras[6].Value = model.Fax;
            paras[7].Value = model.Email;
            paras[8].Value = model.QQ;
            paras[9].Value = model.NickName;
            paras[10].Value = model.Address;
            paras[11].Value = model.RealName;
            paras[12].Value = model.Sex;
            paras[13].Value = model.Enabled;
            paras[14].Value = model.LastLoginTime;
            paras[15].Value = model.CreateUserID;
            paras[16].Value = model.CreateDate;
            paras[17].Value = model.ModifyUserID;
            paras[18].Value = model.ModifyDate;
            paras[19].Value = model.RecordStatus;
            paras[20].Value = model.IsDeleted;

            return paras;
        }
        /*
        private User GetModel(DataRow row)
        {
            User model = new User();
			if(row!=null)
			{
				model.UserID = (( row["UserID"])==DBNull.Value)?0:Convert.ToInt32( row["UserID"]);
				model.UserName =  row["UserName"].ToString();
				model.UserPassword =  row["UserPassword"].ToString();
				model.RoleID = (( row["RoleID"])==DBNull.Value)?0:Convert.ToInt32( row["RoleID"]);
				model.DeptID = (( row["DeptID"])==DBNull.Value)?0:Convert.ToInt32( row["DeptID"]);
				model.Phone =  row["Phone"].ToString();
				model.Fax =  row["Fax"].ToString();
				model.Email =  row["Email"].ToString();
				model.QQ =  row["QQ"].ToString();
				model.NickName =  row["NickName"].ToString();
				model.Address =  row["Address"].ToString();
				model.RealName =  row["RealName"].ToString();
				model.Sex = (bool) row["Sex"];
				model.Enabled = (bool) row["Enabled"];
				model.LastLoginTime = (( row["LastLoginTime"])==DBNull.Value)?Convert.ToDateTime(1900-1-1):Convert.ToDateTime( row["LastLoginTime"]);
				model.CreateUserID = (( row["CreateUserID"])==DBNull.Value)?0:Convert.ToInt32( row["CreateUserID"]);
				model.CreateDate = (( row["CreateDate"])==DBNull.Value)?Convert.ToDateTime(1900-1-1):Convert.ToDateTime( row["CreateDate"]);
				model.ModifyUserID = (( row["ModifyUserID"])==DBNull.Value)?0:Convert.ToInt32( row["ModifyUserID"]);
				model.ModifyDate = (( row["ModifyDate"])==DBNull.Value)?Convert.ToDateTime(1900-1-1):Convert.ToDateTime( row["ModifyDate"]);
				model.RecordStatus =  row["RecordStatus"].ToString();
				model.IsDeleted = (bool) row["IsDeleted"];
			}
			else
			{
				return null;
			}
			return model;
        }
    
        private User GetModel(System.Data.IDataReader dataReader)
        {
            User model = new User();
			if(dataReader!=null)
			{
				model.UserID = (( dataReader["UserID"])==DBNull.Value)?0:Convert.ToInt32( dataReader["UserID"]);
				model.UserName =  dataReader["UserName"].ToString();
				model.UserPassword =  dataReader["UserPassword"].ToString();
				model.RoleID = (( dataReader["RoleID"])==DBNull.Value)?0:Convert.ToInt32( dataReader["RoleID"]);
				model.DeptID = (( dataReader["DeptID"])==DBNull.Value)?0:Convert.ToInt32( dataReader["DeptID"]);
				model.Phone =  dataReader["Phone"].ToString();
				model.Fax =  dataReader["Fax"].ToString();
				model.Email =  dataReader["Email"].ToString();
				model.QQ =  dataReader["QQ"].ToString();
				model.NickName =  dataReader["NickName"].ToString();
				model.Address =  dataReader["Address"].ToString();
				model.RealName =  dataReader["RealName"].ToString();
				model.Sex = (bool) dataReader["Sex"];
				model.Enabled = (bool) dataReader["Enabled"];
				model.LastLoginTime = (( dataReader["LastLoginTime"])==DBNull.Value)?Convert.ToDateTime(1900-1-1):Convert.ToDateTime( dataReader["LastLoginTime"]);
				model.CreateUserID = (( dataReader["CreateUserID"])==DBNull.Value)?0:Convert.ToInt32( dataReader["CreateUserID"]);
				model.CreateDate = (( dataReader["CreateDate"])==DBNull.Value)?Convert.ToDateTime(1900-1-1):Convert.ToDateTime( dataReader["CreateDate"]);
				model.ModifyUserID = (( dataReader["ModifyUserID"])==DBNull.Value)?0:Convert.ToInt32( dataReader["ModifyUserID"]);
				model.ModifyDate = (( dataReader["ModifyDate"])==DBNull.Value)?Convert.ToDateTime(1900-1-1):Convert.ToDateTime( dataReader["ModifyDate"]);
				model.RecordStatus =  dataReader["RecordStatus"].ToString();
				model.IsDeleted = (bool) dataReader["IsDeleted"];
			}
			else
			{
				return null;
			}
			return model;
        }
        */
        #endregion
        /// <summary>
        /// 该数据访问对象的属性值装载到数据库更新参数数组
        /// </summary>
        /// <remarks></remarks>


        /// <summary>
        /// 增加TbuserInfo
        /// </summary>
        /// <param name="info">待插入对象</param>
        /// <returns>int 受影响的行数</returns>
        public bool Insert(User model, SqlTransaction dbt = null)
        {

            SqlParameter[] paras = (SqlParameter[])this.ValueParas(model);

            //是自增长的
            string strInsert = "Insert into [tbUser](";
            strInsert = strInsert + "[UserName],";
            strInsert = strInsert + "[UserPassword],";
            strInsert = strInsert + "[RoleID],";
            strInsert = strInsert + "[DeptID],";
            strInsert = strInsert + "[Phone],";
            strInsert = strInsert + "[Fax],";
            strInsert = strInsert + "[Email],";
            strInsert = strInsert + "[QQ],";
            strInsert = strInsert + "[NickName],";
            strInsert = strInsert + "[Address],";
            strInsert = strInsert + "[RealName],";
            strInsert = strInsert + "[Sex],";
            strInsert = strInsert + "[Enabled],";
            strInsert = strInsert + "[LastLoginTime],";
            strInsert = strInsert + "[CreateUserID],";
            strInsert = strInsert + "[CreateDate],";
            strInsert = strInsert + "[ModifyUserID],";
            strInsert = strInsert + "[ModifyDate],";
            strInsert = strInsert + "[RecordStatus],";
            strInsert = strInsert + "[IsDeleted]";
            strInsert = strInsert + ")";
            strInsert = strInsert + "Values(";
            strInsert = strInsert + "@UserName, ";
            strInsert = strInsert + "@UserPassword, ";
            strInsert = strInsert + "@RoleID, ";
            strInsert = strInsert + "@DeptID, ";
            strInsert = strInsert + "@Phone, ";
            strInsert = strInsert + "@Fax, ";
            strInsert = strInsert + "@Email, ";
            strInsert = strInsert + "@QQ, ";
            strInsert = strInsert + "@NickName, ";
            strInsert = strInsert + "@Address, ";
            strInsert = strInsert + "@RealName, ";
            strInsert = strInsert + "@Sex, ";
            strInsert = strInsert + "@Enabled, ";
            strInsert = strInsert + "@LastLoginTime, ";
            strInsert = strInsert + "@CreateUserID, ";
            strInsert = strInsert + "@CreateDate, ";
            strInsert = strInsert + "@ModifyUserID, ";
            strInsert = strInsert + "@ModifyDate, ";
            strInsert = strInsert + "@RecordStatus, ";
            strInsert = strInsert + "@IsDeleted";
            strInsert = strInsert + ") Select scope_IDENTITY()";
            string reVal;
            if (dbt == null)
            {
                reVal = SqlHelper.ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, strInsert, paras).ToString();
            }
            else
            {
                reVal = SqlHelper.ExecuteScalar(dbt, CommandType.Text, strInsert, paras).ToString();
            }
            model.UserID = Convert.ToInt32(reVal);
            if (model.UserID > 0)
            {

                return true;
            }
            else
            {
                return false;
            }






        }




        /// <summary>
        /// 更新TbuserInfo
        /// </summary>
        /// <param name="info">待更新对象</param>
        /// <returns>int 受影响的行数</returns>
        public int Update(User info, SqlTransaction dbt = null)
        {

            //是自增长的
            string strUpdate = "Update [tbUser] set ";
            strUpdate = strUpdate + "[UserName] = @UserName,";
            strUpdate = strUpdate + "[UserPassword] = @UserPassword,";
            strUpdate = strUpdate + "[RoleID] = @RoleID,";
            strUpdate = strUpdate + "[DeptID] = @DeptID,";
            strUpdate = strUpdate + "[Phone] = @Phone,";
            strUpdate = strUpdate + "[Fax] = @Fax,";
            strUpdate = strUpdate + "[Email] = @Email,";
            strUpdate = strUpdate + "[QQ] = @QQ,";
            strUpdate = strUpdate + "[NickName] = @NickName,";
            strUpdate = strUpdate + "[Address] = @Address,";
            strUpdate = strUpdate + "[RealName] = @RealName,";
            strUpdate = strUpdate + "[Sex] = @Sex,";
            strUpdate = strUpdate + "[Enabled] = @Enabled,";
            strUpdate = strUpdate + "[LastLoginTime] = @LastLoginTime,";
            strUpdate = strUpdate + "[CreateUserID] = @CreateUserID,";
            strUpdate = strUpdate + "[CreateDate] = @CreateDate,";
            strUpdate = strUpdate + "[ModifyUserID] = @ModifyUserID,";
            strUpdate = strUpdate + "[ModifyDate] = @ModifyDate,";
            strUpdate = strUpdate + "[RecordStatus] = @RecordStatus,";
            strUpdate = strUpdate + "[IsDeleted] = @IsDeleted";
            strUpdate = strUpdate + " WHERE ";
            strUpdate = strUpdate + "[UserID] = @UserID";



            SqlParameter[] paras = (SqlParameter[])this.ValueParas(info);

            int reVal = 0;
            if (dbt == null)
            {
                reVal = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, strUpdate.ToString(), paras);
            }
            else
            {
                reVal = SqlHelper.ExecuteNonQuery(dbt, CommandType.Text, strUpdate.ToString(), paras);
            }
            return reVal;
        }

        /// <summary>
        /// 按主键删除TbuserInfo
        /// </summary>
        /// <param name="userid">主键值</param>			

        /// <returns>int 受影响的行数</returns>
        public int Delete(int UserID, SqlTransaction dbt = null)
        {
            string strSql = "DELETE FROM tbUser WHERE UserID=@UserID";
            SqlParameter[] paras = {
                   
                     new SqlParameter("@UserID", SqlDbType.Int)
                    };
            paras[0].Value = UserID;

            int reVal = 0;
            if (dbt == null)
            {
                reVal = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, strSql, paras);
            }
            else
            {
                reVal = SqlHelper.ExecuteNonQuery(dbt, CommandType.Text, strSql, paras);
            }


            return reVal;

        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public User GetModel(int UserID, SqlTransaction dbt = null)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [tbUser] ");
            strSql.Append(" where [UserID] = @UserID");

            SqlParameter[] paras = {
                   
                     new SqlParameter("@UserID", SqlDbType.Int)
                    };
            paras[0].Value = UserID;

            User model = null;

            DataSet ds;
            if (dbt == null)
            {
                ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, strSql.ToString(), paras);
            }
            else
            {
                ds = SqlHelper.ExecuteDataset(dbt, CommandType.Text, strSql.ToString(), paras);
            }

            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                // model = GetModel(ds.Tables[0].Rows[0]);
                model = ds.Tables[0].ToList<User>()[0];
            }
            return model;
        }

        public DataSet GetModelDs(int UserID, SqlTransaction dbt = null)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [tbUser] ");
            strSql.Append(" where [UserID] = @UserID");

            SqlParameter[] paras = {
                   
                     new SqlParameter("@UserID", SqlDbType.Int)
                    };
            paras[0].Value = UserID;

            DataSet ds;
            if (dbt == null)
            {
                ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, strSql.ToString(), paras);
            }
            else
            {
                ds = SqlHelper.ExecuteDataset(dbt, CommandType.Text, strSql.ToString(), paras);
            }

            return ds;
        }
        /*  
          /// <summary>
          /// 获取全部实体集合
          /// </summary>
          /// <returns>List</returns>
          public DataSet GetModelList(string condition, string orderBy = "", SqlParameter[] paras = null, SqlTransaction dbt = null)
          {
              IList< User> list = new List< User>();
              string SelectText = string.Format("SELECT  UserID,UserName,UserPassword,RoleID,DeptID,Phone,Fax,Email,QQ,NickName,Address,RealName,Sex,Enabled,LastLoginTime,CreateUserID,CreateDate,ModifyUserID,ModifyDate,RecordStatus,IsDeleted "+
                "FROM {0} WHERE 1=1 {1} {2}", DBOwner+"tbUser", (!string.IsNullOrEmpty(condition) ? (" AND " + condition) : ""), ((!string.IsNullOrEmpty(orderBy)) ? (" ORDER BY " + orderBy) : ""));
            
              DataSet ds ;
              if(dbt==null)
              {
                  ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, SelectText, paras);
              }
              else
              {
                  ds = SqlHelper.ExecuteDataset(dbt, CommandType.Text, SelectText, paras);
              }
            //  list = ds.Tables[0].ToList<User>();
            
              return ds;
          }
        
          public DataSet GetModelListWithKeys(string keys, User model, string orderBy = "", SqlTransaction dbt = null)
          {
              SqlParameter[] paras = (SqlParameter[])this.ValueParas(model);
              string condition = getCondition(keys);
              return this.GetModelList(condition, orderBy, paras, dbt);
          }
        
          public DataSet GetModelList(NameValueCollection condition, SqlTransaction dbt = null)
          {
              //如果有时间段要求的数据，可以默认查近几个月的数据，如果输入了查询关键字，才不管时间段
              DataSet ds = null;
              string Select = string.Format(@"
                  select * from tbUser
              ");
				
                
              List<SqlParameter> paras = new List<SqlParameter>();
              string where = "where 1=1 ";
              if (!string.IsNullOrEmpty(condition["keyWord"]))
              {
                  //where = string.Format("a.useraccount+a.email+a.phone+a.remarks+r.roleName like '%{0}%'", condition["keyWord"]);
                  where += string.Format(" and     ISNULL(UserID,'')+  ISNULL(UserName,'')+  ISNULL(UserPassword,'')+  ISNULL(RoleID,'')+  ISNULL(DeptID,'')+  ISNULL(Phone,'')+  ISNULL(Fax,'')+  ISNULL(Email,'')+  ISNULL(QQ,'')+  ISNULL(NickName,'')+  ISNULL(Address,'')+  ISNULL(RealName,'')+  ISNULL(Sex,'')+  ISNULL(Enabled,'')+  ISNULL(LastLoginTime,'')+  ISNULL(CreateUserID,'')+  ISNULL(CreateDate,'')+  ISNULL(ModifyUserID,'')+  ISNULL(ModifyDate,'')+  ISNULL(RecordStatus,'')+  ISNULL(IsDeleted,'') like @keyWord");
                  SqlParameter para = new SqlParameter("@keyWord", "%" + condition["keyWord"] + "%");
                  paras.Add(para);
              }
              if (!string.IsNullOrEmpty(condition["detailsearch"]))
              {
                  string detailSearch = condition["detailsearch"];
                  NameValueCollection nvc = detailSearch.SerializeToKeyValues();
                  condition.Remove("detailsearch");
                  condition.Add(nvc);
                  string search_data1 = condition.ToSqlFilter("UserID|UserName|UserPassword|RoleID|DeptID|Phone|Fax|Email|QQ|NickName|Address|RealName|Sex|Enabled|LastLoginTime|CreateUserID|CreateDate|ModifyUserID|ModifyDate|RecordStatus|IsDeleted", true, paras, "");
               
                  where += string.Format(" and 1=1 {0}  ", (!string.IsNullOrEmpty(search_data1) ? (" AND " + search_data1) : ""));
              }

              string orderBy = " order by  UserID ";
              ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, Select + where + orderBy, paras.ToArray());
              return ds;
          }
        
          public bool UpdateOrInsert(User model, SqlTransaction dbt = null)
          {
              bool reVal = false;
             // string keys = "kunnr|yearmonth";
              DataSet ds_model = GetModelListWithKeys(keys, model, "", dbt);
              if (ds_model.Tables[0].Rows.Count > 0)
              {
                  int count = Update(model, dbt);
                  reVal = count > 0 ? true : false;
              }
              else
              {
                  reVal = Insert(model, dbt);

              }

              return reVal;
          }
        
      
       
          /// <summary>
          /// 按条件查询总记录数
          /// </summary>
          /// <param name="condition">条件(AND 列名 = 值)</param>
          /// <returns>int 总记录数</returns>
          public int CountRow(string condition,  SqlTransaction dbt = null)
          {
              if (condition.Equals(""))
              {
                  condition = "1=1";
              }
              string strSql = "SELECT COUNT(*) FROM "+DBOwner+"tbUser WHERE  " + condition;
              string reVal;
              if (dbt == null)
              {
                  reVal = SqlHelper.ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, strSql).ToString();
              }
              else
              {
                  reVal = SqlHelper.ExecuteScalar(dbt, CommandType.Text, strSql).ToString();
              }
              return Convert.ToInt32( reVal);
          }
      
        
   */


    }
    #endregion
}
